<?php
class ModelCatalogProduct extends Model {
	/**
	 * 
	 * Get Product by product id
	 * @author Le Quoc Nam
	 * @param $product_id id of product
	 * @return array of product information
	 */
	public function getProduct($product_id) {
		// Le Quoc Nam rewrite to improve performance 17/11/2012
		$product_data = $this->cache->get('product.detail.'. "." .$product_id);
		if(!$product_data){
	        $SQL = "SELECT DISTINCT p.tax_class_id, p.model, p.description, p.customer_id, p.meta_description, p.viewed, p.price," .
	 	        "p.date_added, cust.username, zone.name as zone ,p.name AS name, p.image, p.shop_id as shop_id, p.manufacturer_id " .
	        	"	  FROM " . DB_PREFIX . "product p " .
	        			" LEFT JOIN " . DB_PREFIX . "zone ON (zone.zone_id = p.zone_id) " .
						" LEFT JOIN " . DB_PREFIX . "customer cust ON (p.customer_id = cust.customer_id) " .
					"WHERE p.product_id = '" . (int)$product_id . "' " .
						"AND p.date_available <= NOW() " .
						"AND p.status = '1'";
			$query = $this->db->query($SQL);
			$product_data = $query->row;
			$this->cache->set('product.detail.' . '.' . $product_id, $product_data);
		}
		return $product_data;
	}
	/**
	 * Get all product of an category
	 * @author Le Quoc Nam
	 * @param $category_id id of category
	 * @param $zone_id id of zone
	 * @param $sort information to sort result array
	 * @param $order order
	 * @param $start begin position 
	 * @param $limit limit number of product
	 * @return array of product
	 */
	public function getProductsByCategoryId($category_id, $zone_id = "" ,$sort = 'p.name', $order = 'DESC', $start = 0, $limit = 20) {
		//---Hoa Tran begin change Oct 17, 2009  ----------------------------------//
		//$sql = "SELECT *, cust.firstname, cust.lastname, p.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN CUSTOMER cust ON ( cust.customer_id = p.customer_id ) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p.language_id = '" . (int)$this->language->getId() . "' AND ss.language_id = '" . (int)$this->language->getId() . "' AND p2c.category_id = '" . (int)$category_id . "'";
		/*
		 * Le Quoc Nam 19/10/2012 - task improve performance
		 * Improve this query, add caching
		 */
		$product_data = $this->cache->get('product.ofcategory.' . $category_id . '.'. $zone_id .'.' .$sort . '.'.$order.'.'. $start);
		if (!$product_data) {
		$SQL  = "SELECT p.name,shop.shop_name, p.viewed,p.date_added,p.image,p.price,p.product_id, " ;
		$SQL .= " zone.name as zone ";
		$SQL .=	" FROM " . DB_PREFIX . "product p " ;
		$SQL .=     " LEFT JOIN " . DB_PREFIX . "shops shop ON (shop.shop_id = p.shop_id) ";
		$SQL .=     " LEFT JOIN " . DB_PREFIX . "zone ON (zone.zone_id = p.zone_id) ";
		$SQL .=		" LEFT JOIN " . DB_PREFIX . "product_to_category p2c " ;
		$SQL .=		" ON (p.product_id = p2c.product_id) " ;
		$SQL .= 	" WHERE p.status = '1' AND p.date_available <= NOW() " ;

		if(mb_strlen($zone_id) > 0 && $zone_id != "3750"){
			$SQL .= 	" AND p.zone_id = '".$zone_id."'" ;
		}
		$SQL .=		" AND p2c.category_id = '" . (int)$category_id . "'";
		//$SQL .=		" AND p.category_id = '" . (int)$category_id . "'";
		//--------------------------------------Hoa Tran end change Oct 17, 2009--//
		$sort_data = array(
			'p.name',
			'p.price',
			'p.date_added',
			'rating'
		);

		if (in_array($sort, $sort_data)) {
			$SQL .= " ORDER BY " . $sort;
		} else {
			$SQL .= " ORDER BY date_added ";
		}

		if ($order == 'DESC') {
			$SQL .= " DESC";
		} else {
			$SQL .= " ASC ";
		}
		$SQL .= " LIMIT " . (int)$start . "," . (int)$limit;
		$query = $this->db->query($SQL);
		$product_data = $query->rows;
		$this->cache->set('product.ofcategory.' . $category_id . '.'. $zone_id .'.' .$sort . '.'.$order.'.'. $start,$product_data);
		}
		return $product_data;
	}


	public function getTotalProductsByCategoryId($category_id = 0, $zone_id = "") {
		/**
		 *Le Quoc Nam 19/10/2012 - task improve performance
		 *Add caching
		 */
		$product_data = $this->cache->get('product.totalProductCategory.' .$category_id .'.'.$zone_id);
		if (!$product_data) {
		$SQL = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_to_category p2c LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id) ";
		$SQL .=		" WHERE p.status = '1' AND p.date_available <= NOW() ";
		if(mb_strlen($zone_id) > 0 && $zone_id != "3750"){
			$SQL .= 	" AND p.zone_id = '".$zone_id."'" ;
		}
		$SQL .=		"  AND p2c.category_id = '" . (int)$category_id . "'";
		$query = $this->db->query( $SQL);
		
		$product_data = $query->row['total'];
		$this->cache->set('product.totalProductCategory.'.$category_id .'.'.$zone_id,$product_data);
		}
		return $product_data;
	}

	public function getProductsByManufacturerId($manufacturer_id, $sort = 'p.name', $order = 'ASC', $start = 0, $limit = 20) {
		$sql = "SELECT *, p.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.status = '1' AND p.date_available <= NOW() AND p.language_id = '" . (int)$this->language->getId() . "' AND ss.language_id = '" . (int)$this->language->getId() . "' AND m.manufacturer_id = '" . (int)$manufacturer_id. "'";

		$sort_data = array(
			'p.name',
			'p.price',
			'p.date_added',
			'rating'
		);

		if (in_array($sort, $sort_data)) {
			$sql .= " ORDER BY " . $sort;
		} else {
			$sql .= " ORDER BY p.name";
		}

		if ($order == 'DESC') {
			$sql .= " DESC";
		} else {
			$sql .= " ASC";
		}


		$sql .= " LIMIT " . (int)$start . "," . (int)$limit;

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalProductsByManufacturerId($manufacturer_id = 0) {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product WHERE status = '1' AND date_available <= NOW() AND manufacturer_id = '" . (int)$manufacturer_id . "'");

		return $query->row['total'];
	}

	public function getProductsByKeyword($keyword, $description = FALSE, $sort = 'p.name', $order = 'ASC', $start = 0, $limit = 20) {
		if ($keyword) {
			//Duc Le beging changing on Jul 11 2010

			//$sql = "SELECT *, p.name AS name, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r WHERE p.product_id = r.product_id GROUP BY r.product_id) AS rating FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.language_id = '" . (int)$this->language->getId() . "' AND ss.language_id = '" . (int)$this->language->getId() . "'";

	$sql  = "SELECT *, " ;
		$sql .= " p.name AS name, zone.name as zone,shop.shop_name, p2c.category_id ,";
		$sql .=	" p.image ," ;
		//$SQL .=	" ss.name AS stock, " ;
		$sql .=	" (SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r " ;  //tai sao them DB_PREFIX
		$sql .= 	"WHERE p.product_id = r.product_id " ;
		$sql .=		"GROUP BY r.product_id) AS rating " ;
		$sql .=	" FROM " . DB_PREFIX . "product p " ;
		//$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd " ;
		//$sql .=		" ON (p.product_id = pd.product_id) " ;
//		$sql .=		" LEFT JOIN " . DB_PREFIX . "manufacturer m " ;
//		$sql .=		" ON (p.manufacturer_id = m.manufacturer_id) " ;
		$sql .=     " LEFT JOIN " . DB_PREFIX . "shops shop ON (shop.shop_id = p.shop_id) ";
//		$SQL .=		" LEFT JOIN " . DB_PREFIX . "stock_status ss " ;
//		$SQL .=		" ON (p.stock_status_id = ss.stock_status_id) " ;
		$sql .=     " LEFT JOIN " . DB_PREFIX . "zone ON (zone.zone_id = p.zone_id) ";
		$sql .=		" LEFT JOIN " . DB_PREFIX . "product_to_category p2c " ;
		$sql .=		" ON (p.product_id = p2c.product_id) " ;
		$sql .= 	" WHERE p.status = '1' AND p.date_available <= NOW() " ;
//		$SQL .= 	" AND p.language_id = '" . (int)$this->language->getId() ;


//End end changing  Jul 22 2010

////
			if (!$description) {
				$sql .= " AND p.name LIKE '%" . $this->db->escape($keyword) . "%'";
			} else {
				$sql .= " AND (p.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.description LIKE '%" . $this->db->escape($keyword) . "%')";
			}
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
			$sort_data = array(
				'p.name',
				'p.price',
				'rating'
//Added on Jul 15 2010
			);
			if (in_array($sort, $sort_data)) {
				$sql .= " ORDER BY " . $sort;
			} else {
				$sql .= " ORDER BY p.name";
			}

//if (in_array($sort, $sort_data)) {
//			$SQL .= " ORDER BY " . $sort;
//		} else {
//			$SQL .= " ORDER BY p.name ";
//		}
// end on jul 15 2010
			if ($order == 'DESC') {
				$sql .= " DESC";
			} else {
				$sql .= " ASC";
			}

			$sql .= " LIMIT " . (int)$start . "," . (int)$limit;

			$query = $this->db->query($sql);

			return $query->rows;
		} else {
			return 0;
		}
	}

	public function getTotalProductsByKeyword($keyword, $description = FALSE) {
		if ($keyword) {
			$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p WHERE p.language_id = '" . (int)$this->language->getId() . "'";

			if (!$description) {
				$sql .= " AND p.name LIKE '%" . $this->db->escape($keyword) . "%'";
			} else {
				$sql .= " AND (p.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.description LIKE '%" . $this->db->escape($keyword) . "%')";
			}
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
			$query = $this->db->query($sql);
			return $query->row['total'];
		} else {
			return 0;
		}
	}
	
	
	//Add by Hai Dang
	public function getTotalProductsByKeyword_Searching($keyword, $description = FALSE, $filter_category_id) {
		if ($keyword) {
			$sql = "SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product p ";
			$sql .=     " LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (p.product_id = p2c.product_id) ";
			$sql .= "WHERE p.language_id = '" . (int)$this->language->getId() . "'";
			if ($filter_category_id != 999) {
				$sql .= " AND p2c.category_id = '" . $filter_category_id . "'";
			}
			if (!$description) {
				$sql .= " AND p.name LIKE '%" . $this->db->escape($keyword) . "%'";
			} else {
				$sql .= " AND (p.name LIKE '%" . $this->db->escape($keyword) . "%' OR p.description LIKE '%" . $this->db->escape($keyword) . "%')";
			}
			$sql .= " AND p.status = '1' AND p.date_available <= NOW()";
			$query = $this->db->query($sql);
			return $query->row['total'];
		} else {
			return 0;
		}
	}
	//End

	/**
	 * Get latest product to display at homepage
	 * @author Le Quoc Nam
	 * @param $limit number of product to get
	 * @return array of newest product
	 */
	public function getLatestProducts($limit) {
		/*
		 * Le Quoc Nam 22/10/2012 - task improve performance
		 * imrpove this query - remove LEFT JOIN
		 */
		$product_data = $this->cache->get('product.latest.' . $this->language->getId() . '.' . $limit);
		if (!$product_data) {
		 $SQL  = " SELECT p.product_id, p.price, p.image, p.name FROM " . DB_PREFIX . " product p ";
		 $SQL .= " WHERE p.status = '1' AND p.date_available <= NOW() ";
		 $SQL .= " ORDER BY p.date_added DESC LIMIT ".(int)$limit;
		 $query = $this->db->query($SQL);
		 $product_data = $query->rows;
		//--------------------------------------Hoa Tran end change Oct 16, 2009--//
		$this->cache->set('product.latest.' . $this->language->getId() . '.' . $limit, $product_data);
		}
		return $product_data;
	}

	public function getBestSellerProducts($limit) {
		$product_data = $this->cache->get('product.bestseller.' . $this->language->getId() . '.' . $limit);

		if (!$product_data) {
			$product_data = array();
			$query = $this->db->query("SELECT op.product_id, SUM(op.quantity) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) WHERE o.order_status_id > '0' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);
			foreach ($query->rows as $result) {
				$product_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product p WHERE p.product_id = '" . (int)$result['product_id'] . "' AND p.status = '1' AND p.date_available <= NOW() AND p.language_id = '" . (int)$this->language->getId() . "'");
				if ($product_query->num_rows) {
					$product_data[] = $product_query->row;
				}
			}
			$this->cache->set('product.bestseller.' . $this->language->getId() . '.' . $limit, $product_data);
		}
		return $product_data;
	}

	public function updateViewed($product_id) {
		$this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = viewed + 1 WHERE product_id = '" . (int)$product_id . "'");
	}

	public function getProductOptions($product_id) {
		$product_option_data = array();

		$product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option WHERE product_id = '" . (int)$product_id . "' ORDER BY sort_order");

		foreach ($product_option_query->rows as $product_option) {
			$product_option_value_data = array();

			$product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "' ORDER BY sort_order");

			foreach ($product_option_value_query->rows as $product_option_value) {
				$product_option_value_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value_description WHERE product_option_value_id = '" . (int)$product_option_value['product_option_value_id'] . "' AND language_id = '" . (int)$this->language->getId() . "'");

				$product_option_value_data[] = array(
					'product_option_value_id' => $product_option_value['product_option_value_id'],
					'name'                    => $product_option_value_description_query->row['name'],
         			'price'                   => $product_option_value['price'],
         			'prefix'                  => $product_option_value['prefix']
				);
			}

			$product_option_description_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_description WHERE product_option_id = '" . (int)$product_option['product_option_id'] . "' AND language_id = '" . (int)$this->language->getId() . "'");

        	$product_option_data[] = array(
        		'product_option_id' => $product_option['product_option_id'],
				'name'              => $product_option_description_query->row['name'],
				'option_value'      => $product_option_value_data,
				'sort_order'        => $product_option['sort_order']
        	);
      	}

		return $product_option_data;
	}

	public function getProductImages($product_id) {
		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_image WHERE product_id = '" . (int)$product_id . "'");

		return $query->rows;
	}

	public function getProductDiscounts($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND quantity > 1 GROUP BY quantity ORDER BY quantity DESC, priority ASC, price ASC");

		return $query->rows;
	}

	public function getProductSpecial($product_id) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}

		$query = $this->db->query("SELECT price FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$customer_group_id . "' AND ((date_start = '0000-00-00' OR date_start < NOW()) AND (date_end = '0000-00-00' OR date_end > NOW())) AND quantity = '1' ORDER BY quantity DESC, priority ASC, price ASC LIMIT 1");

		if ($query->num_rows) {
			return $query->row['price'];
		} else {
			return FALSE;
		}
	}

	public function getProductSpecials($sort = 'p.name', $order = 'ASC', $start = 0, $limit = 20) {
		if ($this->customer->isLogged()) {
			$customer_group_id = $this->customer->getCustomerGroupId();
		} else {
			$customer_group_id = $this->config->get('config_customer_group_id');
		}

		$sql = "SELECT *, p.name AS name, p.price, ps.price AS special, p.image, m.name AS manufacturer, ss.name AS stock, (SELECT AVG(r.rating) " .
			"	FROM " . DB_PREFIX . "review r " .
			"     WHERE p.product_id = r.product_id " .
			"	  GROUP BY r.product_id) AS rating " .
			" FROM " . DB_PREFIX . "product_special ps " .
				" LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) " .
				//" LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) " .
			    " LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) " .
	    		" LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) " .
				" WHERE ps.date_start < NOW() AND ps.date_end > NOW() AND p.status = '1' AND p.date_available <= NOW() AND p.language_id = '" . (int)$this->language->getId() . "' AND ss.language_id = '" . (int)$this->language->getId() . "'";


		$sql .= " ORDER BY p.date_added DESC ";
		$sql .= " LIMIT " . (int)$start . "," . (int)$limit;

		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function getTotalProductSpecials() {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "product_discount pd LEFT JOIN " . DB_PREFIX . "product p ON (pd.product_id = p.product_id) WHERE p.status = '1' AND pd.date_start < NOW() AND pd.date_end > NOW()");

		return $query->row['total'];
	}

	//-------------------------------------------------------------------------//
	// Get list product base on upload customer
	//-------------------------------------------------------------------------//
	public function getProductRelatedShop($product_id, $start = 0, $limit = 20) {
		$product_data = array();
			//---Hoa Tran begin change Oct 17, 2009  ----------------------------------//
			//$product_query = $this->db->query("SELECT DISTINCT *, p.name AS name, p.image, m.name AS manufacturer, ss.name AS stock FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) LEFT JOIN " . DB_PREFIX . "stock_status ss ON (p.stock_status_id = ss.stock_status_id) WHERE p.product_id = '" . (int)$result['related_id'] . "' AND p.language_id = '" . (int)$this->language->getId() . "' AND ss.language_id = '" . (int)$this->language->getId() . "' AND p.date_available <= NOW() AND p.status = '1'");
			$SQL = " SELECT DISTINCT *, p.name AS name,zone.name as zone_name, ";
			$SQL .=	" p.image, m.name AS manufacturer, ss.name AS stock ";
			$SQL .=	" FROM " . DB_PREFIX . "product p ";
			//$SQL .=		"LEFT JOIN " . DB_PREFIX . "product_description pd ";
			//$SQL .=			"ON (p.product_id = pd.product_id) ";
			$SQL.=	" LEFT JOIN " . DB_PREFIX . "zone ";
			$SQL.=			"ON (zone.zone_id = p.zone_id) ";

			$SQL .=		"LEFT JOIN " . DB_PREFIX . "manufacturer m ";
			$SQL .=			"ON (p.manufacturer_id = m.manufacturer_id) ";
			$SQL .=		"LEFT JOIN " . DB_PREFIX . "stock_status ss ";
			$SQL .=			"ON (p.stock_status_id = ss.stock_status_id) ";
			//$SQL .=	" WHERE p.product_id = '" . (int)$result['related_id'] . "' ";
			$SQL .=	" WHERE p.language_id = '" . (int)$this->language->getId() . "' ";
			$SQL .=		" AND ss.language_id = '" . (int)$this->language->getId() . "' ";
			$SQL .=		" AND p.date_available <= NOW() ";
			$SQL .=		" AND p.status = '1'";
			$SQL .= " ORDER BY p.date_added DESC ";
			$SQL .= " LIMIT 0,10";
			//$SQL .= " LIMIT " . (int)$start . "," . (int)$limit;
			$product_query = $this->db->query($SQL);
		return $product_query->rows;
	}

	//-------------------------------------------------------------------------//
	// Get Relation Product base on Specifies product
	//-------------------------------------------------------------------------//
	// Start edit by Q Hieu 07/11/2012
	public function getProductRelated($product_id) {
		$product_data = $this->cache->get('product.related.new' . $this->language->getId() . "." .$product_id);
		if(!$product_data){
			$product_data = array();
			$catagory_id_relate = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
			foreach ($catagory_id_relate->rows as $category_result) {
					$SQL = "SELECT *, p.name AS name, p.image AS image FROM " . DB_PREFIX . "product p ";
					$SQL .= " LEFT JOIN " . DB_PREFIX . "product_to_category pc ON (p.product_id = pc.product_id)";
					$SQL .= " WHERE pc.category_id = '" . (int)$category_result['category_id'] . "' AND p.language_id = '" . (int)$this->language->getId() . "'";
					$SQL .= " AND p.status = '1'  AND p.date_available <= NOW() ORDER BY p.date_added DESC LIMIT 0,8 ";
					$product_query = $this->db->query($SQL);
					$product_data = $product_query->rows;
					
				}
				$this->cache->set('product.related.' . $this->language->getId() . '.' . $product_id, $product_data);
			}
		//}
		return $product_data;
	}
	public function getProductView($product_id) {
		$product_data = $this->cache->get('product.related.view' . $this->language->getId() . "." .$product_id);
		if(!$product_data){
			$product_data = array();
			$catagory_id_relate = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");
			foreach ($catagory_id_relate->rows as $category_result) {
					$SQL = "SELECT *, p.name AS name, p.image AS image FROM " . DB_PREFIX . "product p ";
					$SQL .= " LEFT JOIN " . DB_PREFIX . "product_to_category pc ON (p.product_id = pc.product_id)";
					$SQL .= " WHERE pc.category_id = '" . (int)$category_result['category_id'] . "' AND p.language_id = '" . (int)$this->language->getId() . "'";
					$SQL .= " AND p.status = '1'  AND p.date_available <= NOW() ORDER BY p.viewed DESC LIMIT 0,8 ";
					$product_query = $this->db->query($SQL);
					$product_data = $product_query->rows;
					
				}
				$this->cache->set('product.related.' . $this->language->getId() . '.' . $product_id, $product_data);
			}
		//}
		return $product_data;
	}
	
	public function getAverageRating($product_id) {
		$query = $this->db->query("SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review WHERE status = '1' AND product_id = '" . (int)$product_id . "' GROUP BY product_id");
		
		if (isset($query->row['total'])) {
			return (float)$query->row['total'];
		} else {
			return 0;
		}
	}
	//---Hoa Tran begin add Oct 10, 2009  ----------------------------------//
	public function getProductsByCategoryIdV01($category_id,
				$sort = 'p.date_added',
				$order = 'DESC',
				$start = 0,
				$limit = 5)
	{
		/*
		 * Le Quoc Nam 22/10/2012- improve performance task
		 * Improve query
		 */
		$data = $this->cache->get('product_info.index.' . $this->language->getId() . "." .$category_id);
		if(!$data){
			$SQL =  " SELECT p.product_id AS product_id, p.name AS name, p.image AS image, p.price AS price, zone.name as address ";
			//$SQL.=  " p.name AS name, ";
			//$SQL.=  " shp.shop_name, ";
			//$SQL.=  " shp.shop_id ";
			//$SQL.=  " shp.address as address ";
			$SQL.=	" FROM " . DB_PREFIX . "product p ";
			$SQL.=	" LEFT JOIN " . DB_PREFIX . "product_to_category p2c ";
			$SQL.=			"ON (p.product_id = p2c.product_id) ";
			//$SQL.=	" LEFT JOIN " . DB_PREFIX . "product_description pd ";
			//$SQL.=		"	ON (p.product_id = pd.product_id) ";
			//$SQL.=	" LEFT JOIN " . DB_PREFIX . "shops shp ";
			//$SQL.=			"ON (p.shop_id = shp.shop_id) ";
			//$SQL.=	" LEFT JOIN " . DB_PREFIX . "customer cust ";
			//$SQL.=			"ON (shp.customer_id = cust.customer_id) ";

			$SQL.=	" LEFT JOIN " . DB_PREFIX . "zone ";
			$SQL.=			"ON (zone.zone_id = p.zone_id) ";

			$SQL.=	" WHERE p.status = '1' AND p.date_available <= NOW() ";
			$SQL.=		" AND p2c.category_id = '" . (int)$category_id . "'";
			$SQL .= " GROUP BY p.product_id ";
			// set sort order
			$sort_data = array(
				'p.price',
				'p.date_added'
			);

			if (in_array($sort, $sort_data)) {
				$SQL .= " ORDER BY " . $sort . " DESC ";
			} else {
				$SQL .= " ORDER BY p.date_added DESC ";
			}
			$SQL .= " LIMIT " . (int)$start . "," . (int)$limit;
			$query = $this->db->query($SQL);
			$data = $query->rows;
			$this->cache->set('product_info.index.' . $this->language->getId() . '.' . $category_id, $data);
		}
		return $data;
		//End Le Quoc Nam improve query
	}

	//-------------------------------------------------------------------------//
	// Get list of product to display on index page
	//-------------------------------------------------------------------------//
	public function getLatestProductsIndexPage($category_id)
	{
		$dataReturn = array();
		// load model
		$this->load->model('product_to_catalog/select');
		$this->load->model('catalog/product');
		$this->load->model('catalog/review');
		$this->load->model('customer/customer');
		$this->load->model('tool/seo_url');
		$this->load->helper('image');
				
		//Le Quoc Nam 11/10/2012 (Task improve system efficiency)
		//We need only 4 latest product from the database
		$productListTmp     = $this->getProductsByCategoryIdV01($category_id, 'pd.date_added',null,0,4);
		//End
		foreach ($productListTmp as $result)
		{
			if ($result['image']) {
				$image = $result['image'];
			} else {
				$image = 'no_image.jpg';
			}
			/*
			 * Le Quoc Nam 22/10/2012 - task improve performance
			 * Remove unneccessary information 
			 */
			//$rating   = $this->model_catalog_review->getAverageRating($result['product_id']);
			//$special  = $this->model_catalog_product->getProductSpecial($result['product_id']);
			$path     = $this->model_product_to_catalog_select->getListCategories($result['product_id']);
			//$username = $this->model_customer_customer->getCustomerNameByShopId($result["shop_id"]);
			/*if ($special) {
				$special = $this->currency->format($this->tax->calculate($special, $result['tax_class_id'], $this->config->get('config_tax')));
			} else {
				$special = FALSE;
			}*/
			//-------------------------------------------------------------------------//
			// Rebuild list product for display on screen
			// Start to add code by D-Ngan Aug 14 2012
			//-------------------------------------------------------------------------//
			$name = $this->wrapString($result['name']);
          	$dataReturn[] = array(
          		'product_id'  => $result['product_id'],
          		//'customer_id' => $result['customer_id'],
          		//'username'    => $username,
          		//'address' 	  => $result['address'],
        		'name'    	  => $name,
				//'model'   	  => $result['model'],
            	//'rating'  	  => $rating,
				//'stars'   	  => sprintf($this->language->get('text_stars'), $rating),
				'thumb'   	  => image_resize($image, 210, 210),
          		//9 is default tax_id
            	'price'   	  => $this->currency->format($this->tax->calculate($result['price'], 9 , $this->config->get('config_tax'))),
				//'special' 	  => $special,
				//'href_shop'   => $this->model_tool_seo_url->rewrite($this->url->http('shops/shop&shop=' . $result['shop_id'])),
				//'href_more'   => $this->model_tool_seo_url->rewrite($this->url->http('product/category&path=' . $category_id)),
				'href'    	  => $this->model_tool_seo_url->rewrite($this->url->http('shops/shop_customer&path='. $path .'&product_id=' . $result['product_id']))
          	);
			//End to add code by D-Ngan Aug 14 2012
		}
		//End Le Quoc Nam
		return $dataReturn;
	}

	//-----------------------------------------------------------------------//
	// Start to add code by D-Ngan Aug 14 2012
	// Get product for index page
	//-----------------------------------------------------------------------//
	public function getProductIndexPage()
	{
		$dataReturn = array();
		// load model
		$this->load->model('catalog/category');
		// Get list of parent category
		//$categoryList = $this->model_catalog_category->getCategories(64);
		/*Le Quoc Nam- 11/10/2012 (Task improve system efficiency)
		 * We need only 8 category in the current theme, therefore, I improve this 
		 * function here to avoid null category. With empty category, system will have a 
		 * longer of execution time in database (can't cache, must compare all product) 
		 */
		$categoryList = $this->model_catalog_category->getCategories(64,8);
		// Get the top newlest product for each category
		foreach($categoryList as $key => $value)
		{
			$dataTmp            = $value;
			// get top of the last product (4)
			$productListTmp     = $this->getLatestProductsIndexPage($value['category_id']);
			$dataTmp["Product"] = $productListTmp;
			$dataReturn[]       = $dataTmp;
		}
		return $dataReturn;
	}
	//End add code by D-Ngan Aug 14 2012

	//-------------------------------------------------------------------------//
	// Get Product by Shop ID Hoa Tran Add New
	//-------------------------------------------------------------------------//
	public function getLastProductsByShopId($shop_id, $zone_id, $limit) {
		if( mb_strlen($shop_id) <= 0){
			return null;
		}

		$SQL_Sub = "";
		$SQL_Sub  =	"SELECT  * , p.name AS name ";
		$SQL_Sub .=	" FROM " . DB_PREFIX . "product p ";
		//$SQL_Sub .=	" LEFT JOIN " . DB_PREFIX . "product_description pd ";
		//$SQL_Sub .=		" ON (p.product_id = pd.product_id) ";
		$SQL_Sub .=   " LEFT JOIN " . DB_PREFIX . "zone ON (zone.zone_id = p.zone_id) ";
		$SQL_Sub .=		" WHERE p.status = '1' ";
		$SQL_Sub .=		" AND p.date_available <= NOW() ";
		if(mb_strlen($shop_id) > 0 ){
			$SQL_Sub .=	 " AND p.shop_id = '" .(int)$shop_id."'";
		}
		if(mb_strlen($zone_id) > 0 ){
			$SQL_Sub .=	 " AND zone.zone_id = '" .(int)$zone_id."'";
		}
		$SQL_Sub .= " ORDER BY p.date_modified DESC";
		$SQL_Sub .=		" LIMIT 10 ";
		$product_data = array();
		$product_query = $this->db->query($SQL_Sub);
		if ($product_query->row)
		{
			$product_data[] = $product_query->row;
		}
		return $product_query->rows;
	}


//-------------------------------------------------------------------------//
// Get total record base on Shop ID
//-------------------------------------------------------------------------//
public function getTotalProductsByShopId($shop_id = 0, $zone_id ) {
		$SQL = "SELECT count(1) as total ";
		$SQL .=		" FROM " . DB_PREFIX . "product p ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "stock_status ss ";
		$SQL .=				"ON (p.stock_status_id = ss.stock_status_id) ";
		$SQL .=         " LEFT JOIN " . DB_PREFIX . "zone ON (zone.zone_id = p.zone_id) ";
		$SQL .=		" WHERE p.status = '1' AND p.date_available <= NOW() ";
		$SQL .=		" AND ss.language_id = '" . (int)$this->language->getId()."'";
		if(mb_strlen($shop_id) > 0 ){
				$SQL .=	 " AND p.shop_id = '" .(int)$shop_id."'";
		}
		if(mb_strlen($zone_id) > 0 && $zone_id != "3750" ){
			$SQL .=	 " AND zone.zone_id = '" .(int)$zone_id."'";
		}
	$query = $this->db->query($SQL);
	return $query->row['total'];
}

//-------------------------------------------------------------------------//
// Get product by shop id
//-------------------------------------------------------------------------//
	public function getProductsByShopId($shop_id, $zone_id , $sort = 'p.name',
									$order = 'ASC', $start = 0,
									$limit = 20) {
		$SQL = "SELECT *, ";
		$SQL .=	" p.name AS name, zone.name as zone, shop.shop_name,";
		$SQL .=		"p.image,";
		$SQL .=		"(SELECT AVG(r.rating) FROM " . DB_PREFIX . "review r ";
		$SQL .=				"WHERE p.product_id = r.product_id ";
		$SQL .=				"GROUP BY r.product_id) AS rating ";
		$SQL .=		" FROM " . DB_PREFIX . "product p ";
		//$SQL .=			" LEFT JOIN " . DB_PREFIX . "product_description pd ";
		//$SQL .=				"ON (p.product_id = pd.product_id) ";
//		$SQL .=			" LEFT JOIN " . DB_PREFIX . "manufacturer m ";
//		$SQL .=				"ON (p.manufacturer_id = m.manufacturer_id) ";
		$SQL .=         " LEFT JOIN " . DB_PREFIX . "zone ON (zone.zone_id = p.zone_id) ";
		$SQL .=         " LEFT JOIN " . DB_PREFIX . "shops shop ON (shop.shop_id = p.shop_id) ";
//		$SQL .=			"LEFT JOIN " . DB_PREFIX . "stock_status ss ";
//		$SQL .=				"ON (p.stock_status_id = ss.stock_status_id) ";
		$SQL .=			"LEFT JOIN " . DB_PREFIX . "product_to_category p2c ";
		$SQL .=				"ON (p.product_id = p2c.product_id) ";
		$SQL .=		" WHERE p.status = '1' AND p.date_available <= NOW() ";
		//$SQL .=		" AND p.language_id = '" . (int)$this->language->getId()."'";
		//$SQL .=		" AND ss.language_id = '" . (int)$this->language->getId()."'";
		if(mb_strlen($shop_id) > 0 ){
				$SQL .=	 " AND p.shop_id = '" .(int)$shop_id."'";
		}

		if(mb_strlen($zone_id) > 0 && $zone_id != "3750" ){
				$SQL .=	 " AND zone.zone_id = '" .(int)$zone_id."'";
		}

		$SQL .= " GROUP BY p.product_id ";
		$sort_data = array(
				'p.name',
				'p.price',
				'rating'
		);

		if (in_array($sort, $sort_data)) {
			$SQL .= " ORDER BY " . $sort;
		} else {
			$SQL .= " ORDER BY p.name";
		}

		if ($order == 'DESC') {
			$SQL .= " DESC";
		} else {
			$SQL .= " ASC";
		}
		$SQL .= " LIMIT " . (int)$start . "," . (int)$limit;
		$query = $this->db->query($SQL);
		return $query->rows;
	}

	//-------------------------------------------------------------------------//
	// Wrap a String incase length of string is too length
	//-------------------------------------------------------------------------//
	public function wrapString($str){
		$name = $str;
		$unicode = mb_detect_encoding($name);
		$array_code  = array("ASCII");
		$array_code1 = array("UTF-8");
		if(in_array($unicode, $array_code)){
			if(mb_strlen($name) > 25){
				$pos = strpos($name, " ", 20);
				$name = mb_substr($name,0, $pos)." ...";
			}
		}else if(in_array($unicode, $array_code1)){
			if(mb_strlen($name) > 35){
				$pos = strpos($name, " ", 30);
				$name = mb_substr($name, 0, $pos)." ...";
			}
		}
		return $name;
	}

	//-------------------------------------------------------------------------//
	// Wrap a String incase length of string is too length
	//-------------------------------------------------------------------------//
	public function wrapString1($str){
		$name = $str;
		$unicode = mb_detect_encoding($name);
		$array_code  = array("ASCII");
		$array_code1 = array("UTF-8");
		if(in_array($unicode, $array_code)){
			if(mb_strlen($name) > 20){
				$name = substr($name,0, 20)."...";
			}
		}else if(in_array($unicode, $array_code1)){
			if(mb_strlen($name) > 25){
				$name = substr($name,0, 25)."...";
			}
		}
		return $name;
	}
	//-----------------------------------------------------------------------//
	//Get modst view prodcut function
	//@Author: D-Ngan
	//-----------------------------------------------------------------------//
	public function  getBestViewProduct($limit,$date){
		/*
		 * Le Quoc Nam - improve performance task
		 * Improve query - add caching
		 */
		//Add cache by Le Quoc Nam
		$product_data = $this->cache->get('product.bestview.' . $this->language->getId() . '.' . $limit);
		if (!$product_data) {
		$SQL=' SELECT p.price, p.image, p.product_id FROM product p'; 
		//$SQL.=' FROM product p LEFT JOIN product_description d ON p.product_id=d.product_id';
		$SQL.=' WHERE TO_DAYS(NOW())-TO_DAYS(date_added)<'.$date;
		$SQL.=' ORDER BY viewed DESC LIMIT '.$limit.';';
		$query = $this->db->query($SQL);
		$product_data = $query->rows;
		$this->cache->set('product.bestview.' . $this->language->getId() . '.' . $limit, $product_data);
		}
		return $product_data;
	}
	//End add code 
}
?>